package com.zkyc.framework.website.mapper;

import com.alibaba.fastjson.JSONObject;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.zkyc.framework.common.pojo.Column;
import com.zkyc.framework.common.pojo.MediaTag;
import com.zkyc.framework.common.vo.website.Media;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Select;

import java.util.Date;
import java.util.List;

/**
 * (Column)表数据库访问层
 *
 * @author libingchuan
 * @since 2022-05-25 15:04:30
 */
@Mapper
public interface MediaMapper {

    @Select("SELECT " +
            "  a.*, " +
            "  d.media_tag_name, " +
            "  e.subject_name  " +
            "FROM " +
            "  system_file a, " +
            "  system_file_ref_tag b, " +
            "  system_file_ref_subject c, " +
            "  system_media_tag d, " +
            "  system_media_subject e  " +
            "WHERE " +
            "  a.id = b.file_id  " +
            "  AND a.id = c.file_id  " +
            "  AND b.media_tag_id = d.id  " +
            "  AND c.media_tag_id = e.id  " +
            "  AND a.id = #{id}")
    Media queryMediaById(Integer id);

    @Select({"<script>" +
            "SELECT " +
            "  a.*, " +
            "  d.media_tag_name, " +
            "  e.id as subjectId, " +
            "  e.subject_name,  " +
            "  g.content_title  " +
            "FROM " +
            "  system_file a, " +
            "  system_file_ref_tag b, " +
            "  system_file_ref_subject c, " +
            "  system_media_tag d, " +
            "  system_media_subject e,  " +
            "  web_content g, " +
            "  system_file_ref_content f " +
            "WHERE " +
            "  a.id = b.file_id  " +
            "  AND a.belong_key = #{belongKey}  " +
            "  AND a.website_id = #{websiteId}  " +
            "  AND a.id = c.file_id  " +
            "  AND b.media_tag_id = d.id  " +
            "  AND c.media_tag_id = e.id  " +
            "  AND a.is_deleted = 0  " +
            "  AND g.status = 1  " +
            "  AND g.is_deleted = 0  " +
            " <if test='type!=null and type !=\"\" '>" +
            " and a.type = #{type} " +
            " </if>" +
            " <if test='subjectId!=null '>" +
            " and e.id = #{subjectId} " +
            " </if>" +
            " <if test='tagId!=null '>" +
            " and d.id = #{tagId} " +
            " </if>" +
            " <if test='description!=null and description !=\"\" '>" +
            " and (a.description like  CONCAT('%',#{description},'%') or g.content_title like  CONCAT('%',#{description},'%')   ) " +
            " </if>" +
            " <if test='dateType == 0 and beginDate != \"\" '>" +
            " and a.create_time BETWEEN #{beginDate} AND #{endDate} " +
            " </if>" +
            " <if test='dateType == 1 and beginDate != \"\" '>" +
            " and a.shot_time BETWEEN #{beginDate} AND #{endDate} " +
            " </if>" +
            " <if test='isContent == 1 '>" +
            " and a.id = f.file_id and g.id = f.content_id " +
            " </if>" +
            " order by g.gmt_updated desc" +
            "</script>"
    })
    List<Media> queryList(String type, String subjectId, Integer tagId, String description, Date createTime,
                          String belongKey, String websiteId, String beginDate, String endDate, String dateType, String isContent);

    @Select({"<script>" +
            "SELECT " +
            "  a.*, " +
            "  d.media_tag_name, " +
            "  e.id as subjectId, " +
            "  e.subject_name " +
            "FROM " +
            "  system_file a, " +
            "  system_file_ref_tag b, " +
            "  system_file_ref_subject c, " +
            "  system_media_tag d, " +
            "  system_media_subject e  " +
            "WHERE " +
            "  a.id = b.file_id  " +
            "  AND a.belong_key = #{belongKey}  " +
            "  AND a.website_id = #{websiteId}  " +
            "  AND a.id = c.file_id  " +
            "  AND b.media_tag_id = d.id  " +
            "  AND c.media_tag_id = e.id  " +
            "  AND a.is_deleted = 0  " +
            " <if test='type!=null and type !=\"\" '>" +
            " and a.type = #{type} " +
            " </if>" +
            " <if test='subjectId!=null '>" +
            " and e.id = #{subjectId} " +
            " </if>" +
            " <if test='tagId!=null '>" +
            " and d.id = #{tagId} " +
            " </if>" +
            " <if test='description!=null and description !=\"\" '>" +
            " and a.description = #{description} " +
            " </if>" +
            " <if test='userId!=null and userId !=\"\" '>" +
            " and a.uploader = #{userId} " +
            " </if>" +
            " <if test='dateType == 0 and beginDate != \"\" '>" +
            " and a.create_time BETWEEN #{beginDate} AND #{endDate} " +
            " </if>" +
            " <if test='dateType == 1 and beginDate != \"\" '>" +
            " and a.shot_time BETWEEN #{beginDate} AND #{endDate} " +
            " </if>" +
            " order by a.update_time desc" +
            "</script>"
    })
    List<Media> queryMediaList(String type, String subjectId, Integer tagId, String description, Date createTime,
                               String belongKey, String websiteId, String beginDate, String endDate, String dateType, String userId);


    @Select("select media_tag_id from system_file_ref_tag where file_id = #{fileId}")
    List<Integer> queryTagIdList(Long fileId);

    @Select("select tag_group_id,id,media_tag_name from system_media_tag where id = #{tagRefId}")
    JSONObject queryTagByTagId(Integer tagRefId);

    @Select("select * from system_media_tag where media_tag_name = ' ' limit 0,1")
    MediaTag queryNullTag();

    @Select({"<script>" +
            "SELECT " +
            " * " +
            "FROM " +
            "  system_file a " +
            "WHERE " +
            "  a.belong_key = 1  " +
            "  AND a.is_deleted = 0  " +
            "  AND a.website_id = #{websiteId} " +
            " <if test='type!=null and type !=\"\" '>" +
            " and a.type = #{type} " +
            " </if>" +
            " <if test='beginDate != null '>" +
            " and a.create_time BETWEEN #{beginDate} AND #{endDate} " +
            " </if>" +
            " order by a.create_time desc" +
            "</script>"
    })
    List<Media> queryOtherFile(String websiteId, String type, String beginDate, String endDate);

}

